
if exists(select name from sysobjects where name = 'sp_DiemGetByHocKy')
	drop proc sp_DiemGetByHocKy
go
/*
	Created by: Phuoc NH
	Created on: 04/012/2013 (dd/mm/yyyy)
	Description: 
	--

	File:$sql-scripts/Proc.sp_DiemGetByHocKy.sql
*/

create proc sp_DiemGetByHocKy
@MaSinhVien varchar(50),
@NamHoc varchar(50)
as
begin
	--select distinct a.MaSinhVien, b.Diem, c.TenMon, c.MaMon, f.SoTC from SinhVien a inner join BangDiem b on a.MaSinhVien=b.MaSinhVien and a.MaSinhVien=@MaSinhVien inner join Mon c on c.MaMon=b.MaMon inner join lop d on a.MaLop =d.MaLop inner join ChuongTrinhDT e on d.MaCT=e.MaCT inner join ChuongTrinhCT f on f.MaCT=e.MaCT 
	--where b.NamHoc+':'+b.HocKy=@NamHoc
	declare @k varchar(50)
set @k=(select distinct c.MaCT from SinhVien a inner join Lop b on a.MaLop=b.MaLop and a.MaSinhVien=@MaSinhVien inner join ChuongTrinhCT c on c.MaCT=b.MaCT )
	select a.MaSinhVien, c.TenMon, b.NamHoc, b.Diem from SinhVien a inner join BangDiem b on a.MaSinhVien=b.MaSinhVien and a.MaSinhVien=@MaSinhVien inner join Mon c on b.MaMon=c.MaMon inner join ChuongTrinhCT d on c.MaMon=d.MaMon and d.MaCT=@k
where b.NamHoc+':'+b.HocKy=@NamHoc
end

